package com.gitee.neuray.security.generator.strategy;

import java.util.List;

/**
 * @author: zhhongyu
 * @description: oracle策略
 * @since: Create in 14:31 2020/2/14
 */
public class OracleStrategy extends DataSourceStrategy {
    @Override
    public StringBuilder queryColumnsSQL(String viewOrtable) {
        StringBuilder sql = new StringBuilder(32);
        sql.append(" select temp.column_name columnname, ");
        sql.append("  temp.data_type dataType, ");
        sql.append("  temp.comments columnComment, ");
        sql.append("  case temp.constraint_type when 'P' then 'PRI' when 'C' then 'UNI' else '' end \"COLUMNKEY\", ");
        sql.append("       '' \"EXTRA\", ");
        sql.append("       temp.data_length dataLength, ");
        sql.append("       'false' queryIf, ");
        sql.append("       'true' listIf ");
        sql.append("  from ( ");
        sql.append("       select col.column_id, ");
        sql.append("      col.column_name, ");
        sql.append("      col.data_type, ");
        sql.append("       col.data_length, ");
        sql.append("       colc.comments, ");
        sql.append("       uc.constraint_type, ");
        sql.append("      row_number() over (partition by col.column_name order by uc.constraint_type desc) as row_flg ");
        sql.append("      from user_tab_columns col ");
        sql.append("      left join user_col_comments colc ");
        sql.append("     on colc.table_name = col.table_name ");
        sql.append("      and colc.column_name = col.column_name ");
        sql.append("      left join user_cons_columns ucc ");
        sql.append("     on ucc.table_name = col.table_name ");
        sql.append("    and ucc.column_name = col.column_name ");
        sql.append("     left join user_constraints uc ");
        sql.append("     on uc.constraint_name = ucc.constraint_name ");
        sql.append("     where col.table_name = upper('");
        sql.append(viewOrtable.toUpperCase());
        sql.append("') ");
        sql.append("  ) temp   ");
        sql.append("  where temp.row_flg = 1   ");
        sql.append("  order by temp.column_id    ");
        return sql;
    }

    @Override
    public StringBuilder queryTablesSQL() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select dt.table_name tableName, ");
        sql.append(" dtc.comments tableComment, ");
        sql.append(" uo.created createTime ");
        sql.append(" from user_tables dt, ");
        sql.append("        user_tab_comments dtc, ");
        sql.append("        user_objects uo ");
        sql.append(" where dt.table_name = dtc.table_name and dt.table_name = uo.object_name and uo.object_type='TABLE' ");
        sql.append(" order by dt.table_name asc ");
        return sql;
    }

    @Override
    public StringBuilder queryTableSQL(String tableName) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select dt.table_name tableName,dtc.comments tableComment,dt.last_analyzed createTime from user_tables dt,user_tab_comments dtc where dt.table_name=dtc.table_name ");
        sql.append( " and dt.table_name = UPPER('");
        sql.append(tableName);
        sql.append("') ");
        return sql;
    }


}
